<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>User-defined Aggregates</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="C-Language Functions"
HREF="xfunc-c.html"><LINK
REL="NEXT"
TITLE="User-defined Types"
HREF="xtypes.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="C-Language Functions"
HREF="xfunc-c.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 35. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="User-defined Types"
HREF="xtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="XAGGR"
>35.10. User-defined Aggregates</A
></H1
><P
>   Aggregate functions  in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
   are expressed in terms of <I
CLASS="FIRSTTERM"
>state values</I
>
   and <I
CLASS="FIRSTTERM"
>state transition functions</I
>.
   That is, an aggregate operates using a state value that is updated
   as each successive input row is processed.
   To define a new aggregate
   function, one selects a data type for the state value,
   an initial value for the state, and a state transition
   function.  The state transition function is just an
   ordinary function that could also be used outside the
   context of the aggregate.  A <I
CLASS="FIRSTTERM"
>final function</I
>
   can also be specified, in case the desired result of the aggregate
   is different from the data that needs to be kept in the running
   state value.
  </P
><P
>   Thus, in addition to the argument and result data types seen by a user
   of the aggregate, there is an internal state-value data type that
   might be different from both the argument and result types.
  </P
><P
>   If we define an aggregate that does not use a final function,
   we have an aggregate that computes a running function of
   the column values from each row.  <CODE
CLASS="FUNCTION"
>sum</CODE
>  is  an
   example  of  this  kind  of aggregate.  <CODE
CLASS="FUNCTION"
>sum</CODE
> starts at
   zero and always adds the current  row's  value  to
   its  running  total.  For example, if we want to make a <CODE
CLASS="FUNCTION"
>sum</CODE
>
   aggregate to work on a data type for complex numbers,
   we only need the addition function for that data type.
   The aggregate definition would be:

</P><PRE
CLASS="SCREEN"
>CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);

SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)</PRE
><P>

   (Notice that we are relying on function overloading: there is more than
    one aggregate named <CODE
CLASS="FUNCTION"
>sum</CODE
>, but
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can figure out which kind
   of sum applies to a column of type <TT
CLASS="TYPE"
>complex</TT
>.)
  </P
><P
>   The above definition of <CODE
CLASS="FUNCTION"
>sum</CODE
> will return zero (the initial
   state condition) if there are no nonnull input values.
   Perhaps we want to return null in that case instead &mdash; the SQL standard
   expects <CODE
CLASS="FUNCTION"
>sum</CODE
> to behave that way.  We can do this simply by
   omitting the <TT
CLASS="LITERAL"
>initcond</TT
> phrase, so that the initial state
   condition is null.  Ordinarily this would mean that the <TT
CLASS="LITERAL"
>sfunc</TT
>
   would need to check for a null state-condition input.  But for
   <CODE
CLASS="FUNCTION"
>sum</CODE
> and some other simple aggregates like
   <CODE
CLASS="FUNCTION"
>max</CODE
> and <CODE
CLASS="FUNCTION"
>min</CODE
>,
   it is sufficient to insert the first nonnull input value into
   the state variable and then start applying the transition function
   at the second nonnull input value.  <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
   will do that automatically if the initial condition is null and
   the transition function is marked <SPAN
CLASS="QUOTE"
>"strict"</SPAN
> (i.e., not to be called
   for null inputs).
  </P
><P
>   Another bit of default behavior for a <SPAN
CLASS="QUOTE"
>"strict"</SPAN
> transition function
   is that the previous state value is retained unchanged whenever a
   null input value is encountered.  Thus, null values are ignored.  If you
   need some other behavior for null inputs, do not declare your
   transition function as strict; instead code it to test for null inputs and
   do whatever is needed.
  </P
><P
>   <CODE
CLASS="FUNCTION"
>avg</CODE
> (average) is a more complex example of an aggregate.
   It requires
   two pieces of running state: the sum of the inputs and the count
   of the number of inputs.  The final result is obtained by dividing
   these quantities.  Average is typically implemented by using an
   array as the state value.  For example,
   the built-in implementation of <CODE
CLASS="FUNCTION"
>avg(float8)</CODE
>
   looks like:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0,0}'
);</PRE
><P>

   (<CODE
CLASS="FUNCTION"
>float8_accum</CODE
> requires a three-element array, not just
   two elements, because it accumulates the sum of squares as well as
   the sum and count of the inputs.  This is so that it can be used for
   some other aggregates besides <CODE
CLASS="FUNCTION"
>avg</CODE
>.)
  </P
><P
>   Aggregate functions can use polymorphic
   state transition functions or final functions, so that the same functions
   can be used to implement multiple aggregates.
   See <A
HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
>Section 35.2.5</A
>
   for an explanation of polymorphic functions.
   Going a step further, the aggregate function itself can be specified
   with polymorphic input type(s) and state type, allowing a single
   aggregate definition to serve for multiple input data types.
   Here is an example of a polymorphic aggregate:

</P><PRE
CLASS="PROGRAMLISTING"
>CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);</PRE
><P>

   Here, the actual state type for any aggregate call is the array type
   having the actual input type as elements.  The behavior of the aggregate
   is to concatenate all the inputs into an array of that type.
   (Note: the built-in aggregate <CODE
CLASS="FUNCTION"
>array_agg</CODE
> provides similar
   functionality, with better performance than this definition would have.)
  </P
><P
>   Here's the output using two different actual data types as arguments:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spclocation,spcacl}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid::regtype)
    FROM pg_attribute
    WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |        array_accum        
---------------+---------------------------
 pg_tablespace | {name,oid,text,aclitem[]}
(1 row)</PRE
><P>
  </P
><P
>   A function written in C can detect that it is being called as an
   aggregate transition or final function by calling
   <CODE
CLASS="FUNCTION"
>AggCheckCallContext</CODE
>, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>if (AggCheckCallContext(fcinfo, NULL))</PRE
><P>
   One reason for checking this is that when it is true for a transition
   function, the first input
   must be a temporary transition value and can therefore safely be modified
   in-place rather than allocating a new copy.
   See <TT
CLASS="LITERAL"
>int8inc()</TT
> for an example.
   (This is the <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>only</I
></SPAN
>
   case where it is safe for a function to modify a pass-by-reference input.
   In particular, aggregate final functions should not modify their inputs in
   any case, because in some cases they will be re-executed on the same
   final transition value.)
  </P
><P
>   For further details see the
   <A
HREF="sql-createaggregate.html"
>CREATE AGGREGATE</A
>
   command.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xfunc-c.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xtypes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>C-Language Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>User-defined Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>